using System;
using System.Collections.Generic;
using System.Text;
using DTO;
using System.Data;
using System.Windows.Forms;
using System.Data.Odbc;
using System.Collections;
using System.Security.Cryptography;
using System.Web.Mobile;
using System.Web.UI.MobileControls;
using System.Web.RegularExpressions;
using System.Text.RegularExpressions;



namespace DAO
{
    public class DocGiaDAO
    {
        DataProvider _provider = new DataProvider();
        public DocGiaDAO()
        {
            _provider.connect();
        }

        static public DataTable GetTableDocGia()
        {
            try
            {
                DataProvider dataprovider = new DataProvider();
                dataprovider.connect();
                String stringSqlQrery = "Select* from DocGia";
                OdbcDataAdapter da = dataprovider.OdbcAdapter(stringSqlQrery);
                DataTable dt = new DataTable();
                da.Fill(dt);
                dataprovider.disconnect();
                return dt;
            }
            catch (Exception ex)
            {

            }
            return new DataTable();
        }
        private DocGiaDTO GetDataFromDataRow(DataTable dt, int i)
        {
            DocGiaDTO info = new DocGiaDTO();
            info.MaDocGia = dt.Rows[i]["MaDocGia"].ToString();
            info.MatKhau = dt.Rows[i]["MatKhau"].ToString();
            info.HoTen = dt.Rows[i]["HoTen"].ToString();
            info.DiaChi = dt.Rows[i]["DiaChi"].ToString();
            info.Email = dt.Rows[i]["Email"].ToString();
            info.NgaySinh = DateTime.Parse(dt.Rows[i]["NgaySinh"].ToString());
            info.DienThoai = dt.Rows[i]["DienThoai"].ToString();
            return info;
        }

        public DocGiaDTO GetDocGia(string madocgia)
        {
            string query = "select *from DocGia where MaDocGia='" + madocgia + "'";
            OdbcDataAdapter adapter = _provider.OdbcAdapter(query);
            DataSet ds = new DataSet();
            adapter.Fill(ds);
            DataTable dt = ds.Tables[0];
            DocGiaDTO info = new DocGiaDTO();
            info = GetDataFromDataRow(dt, 0);
            return info;
        }

        public ArrayList GetDocGia()
        {
            ArrayList arr;
            string query = "select *from DocGia";
            OdbcDataAdapter adapter = _provider.OdbcAdapter(query);
            DataSet ds = new DataSet();
            adapter.Fill(ds);
            DataTable dt = ds.Tables[0];
            arr = new ArrayList(dt.Rows.Count);
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                DocGiaDTO dg = new DocGiaDTO();
                dg = GetDataFromDataRow(dt, i);
                arr.Add(dg);
            }
            return arr;
        }
        public int GetSoMauTin()
        {
            string query = "select *from DocGia";
            OdbcDataAdapter adapter = _provider.OdbcAdapter(query);
            int somautin = 0;
            try
            {
                DataSet ds = new DataSet();
                adapter.Fill(ds);
                DataTable table = ds.Tables[0];
                somautin = table.Rows.Count;
            }
            catch (Exception)
            {
                somautin = -1;
            }
            return somautin;
        }        
        public void insert(DocGiaDTO info)
        {
            //Kiem tra ma doc gia co trung khong ?
            string temp = MD5(info.MatKhau);
            String query = "select * from DocGia  where MaDocGia='" + info.MaDocGia + "'";
            OdbcDataAdapter adapter;
            adapter = _provider.OdbcAdapter(query);
            DataSet ds = new DataSet();
            adapter.Fill(ds);
            DataTable dt;
            dt = ds.Tables[0];
            if (dt.Rows.Count > 0)
            {
                MessageBox.Show("UserName nay da ton tai");
                return;
            }
            //Neu khong trung thi insert
            string cmdInsert = "Insert into DocGia values('" + info.MaDocGia + "','" +
            temp + "','" + info.HoTen + "','" + info.DiaChi + "','" + info.Email + "','" + info.NgaySinh + "','"+info.DienThoai+"')";
            _provider.executeNonQuery(cmdInsert);            
        }
        public void UpdateThongTin(DocGiaDTO info)
        {
            string cmdupdate = "update DocGia set HoTen='" + info.HoTen +
            "',DiaChi='" + info.DiaChi + "',Email=" + info.Email + ",NgaySinh='" + info.NgaySinh +
            "',DienThoai='" + info.DienThoai + "' where MaDocGia='" + info.MaDocGia + "'";
            _provider.executeNonQuery(cmdupdate);            
        }
        /*public void UpdateMauKhau(string ma,string username,string pass)
        {
            string temp = EncodePassword(pass);
            string query = "select * from KhachHang  where UserName='" + username + "'";
            OleDbDataAdapter adapter = _provider.oledbadapter(query);
            DataSet ds = new DataSet();
            adapter.Fill(ds);
            DataTable dt;
            dt = ds.Tables[0];
            if (dt.Rows.Count > 0)
            {
                MessageBox.Show("UserName nay da ton tai");
                return;
            }          
            string cmdupdate = "update KhachHang set UserName='" + username + "',Pass='" + temp + "' where MaKhachHang='" + makh + "'";
            _provider.executeNonQuery(cmdupdate);
            MessageBox.Show("update thanh cong");
        }*/
        public void Delete(string madocgia)
        {
            string cmddelete = "delete from DocGia where MaDocGia='" + madocgia + "'";
            _provider.executeNonQuery(cmddelete);            
        }

        private String MD5(string stringInput)
        {

            byte[] HashValue, MessageBytes = new UnicodeEncoding().GetBytes(stringInput);
            MD5 md5hash = new MD5CryptoServiceProvider();
            string strHex = "";
            HashValue = md5hash.ComputeHash(MessageBytes);
            foreach (byte b in HashValue)
            {
                strHex += String.Format("{0:x2}", b);
            }
            return strHex;
        }
        public bool KiemTraDangNhap(string username, string matkhau)
        {
            bool flag = false;
            String temp = MD5(matkhau);
            String query = "select * from DocGia  where MaDocGia='" + username + "' and MatKhau='"+temp+"'";
            OdbcDataAdapter adapter;
            adapter = _provider.OdbcAdapter(query);
            DataSet ds = new DataSet();
            adapter.Fill(ds);
            DataTable dt;
            dt = ds.Tables[0];
            if (dt.Rows.Count > 0)
            {
                flag = true;
            }
            return flag;
        }
    }
}
